import pandas as pd
import numpy as np
from datetime import datetime, timedelta, date
from typing import Dict, List, Optional
import logging
from sqlalchemy.orm import Session
from sqlalchemy import and_

from backend.config.database import get_db_session
from backend.entities.load_data_new import LoadData
from backend.entities.weather_daily import WeatherDaily
from backend.entities.holiday import HolidayInfo
from backend.utils.entsoe_api import ENTSOEAPIService
from backend.utils.weather_api import WeatherAPIService
from chinese_calendar import is_holiday

logger = logging.getLogger(__name__)

class DataUpdateService:
    """数据更新服务"""
    
    def __init__(self):
        self.entsoe_api = ENTSOEAPIService()
        self.weather_api = WeatherAPIService()
    
    def update_yesterday_data(self) -> Dict:
        """
        更新前一天的数据到数据库
        
        Returns:
            Dict包含更新结果
        """
        try:
            # 获取前一天的日期
            yesterday = (datetime.now() - timedelta(days=1)).date()
            yesterday_str = yesterday.strftime('%Y-%m-%d')
            
            logger.info(f"开始更新 {yesterday_str} 的数据")
            
            # 验证API token
            if not self.entsoe_api.validate_token():
                return {
                    'success': False,
                    'error': 'ENTSO-E API token验证失败'
                }
            
            # 获取负荷数据
            load_df = self.entsoe_api.get_load_data(yesterday_str, yesterday_str)
            if load_df is None or load_df.empty:
                return {
                    'success': False,
                    'error': f'无法获取 {yesterday_str} 的负荷数据'
                }
            
            # 获取气象数据（使用新的气象API服务）
            weather_df = self.weather_api.get_weather_data(yesterday_str, yesterday_str)
            if weather_df is None or weather_df.empty:
                logger.warning(f"无法获取 {yesterday_str} 的气象数据，使用默认值")
                weather_df = self._generate_default_weather_data(yesterday)
            
            # 获取节假日信息
            holiday_info = self._get_holiday_info(yesterday)
            
            # 保存到数据库
            db = get_db_session()
            try:
                # 保存负荷数据
                load_saved = self._save_load_data(load_df, yesterday, db)
                
                # 保存气象数据
                weather_saved = self._save_weather_data(weather_df, db)
                
                # 保存节假日信息
                holiday_saved = self._save_holiday_info(holiday_info, db)
                
                db.commit()
                
                return {
                    'success': True,
                    'message': f'成功更新 {yesterday_str} 的数据',
                    'details': {
                        'load_records': load_saved,
                        'weather_records': weather_saved,
                        'holiday_records': holiday_saved,
                        'date': yesterday_str
                    }
                }
                
            except Exception as e:
                db.rollback()
                logger.error(f"保存数据到数据库失败: {e}")
                return {
                    'success': False,
                    'error': f'保存数据失败: {str(e)}'
                }
            finally:
                db.close()
                
        except Exception as e:
            logger.error(f"更新前一天数据失败: {e}")
            return {
                'success': False,
                'error': str(e)
            }
    
    def update_custom_date_data(self, target_date: str) -> Dict:
        """
        更新指定日期的数据到数据库
        
        Args:
            target_date: 目标日期 (YYYY-MM-DD)
        
        Returns:
            Dict包含更新结果
        """
        try:
            # 验证日期格式
            try:
                target_dt = datetime.strptime(target_date, '%Y-%m-%d').date()
            except ValueError:
                return {
                    'success': False,
                    'error': '日期格式错误，请使用YYYY-MM-DD格式'
                }
            
            logger.info(f"开始更新 {target_date} 的数据")
            
            # 验证API token
            if not self.entsoe_api.validate_token():
                return {
                    'success': False,
                    'error': 'ENTSO-E API token验证失败'
                }
            
            # 获取负荷数据
            load_df = self.entsoe_api.get_load_data(target_date, target_date)
            if load_df is None or load_df.empty:
                return {
                    'success': False,
                    'error': f'无法获取 {target_date} 的负荷数据'
                }
            
            # 获取气象数据（使用新的气象API服务）
            weather_df = self.weather_api.get_weather_data(target_date, target_date)
            if weather_df is None or weather_df.empty:
                logger.warning(f"无法获取 {target_date} 的气象数据，使用默认值")
                weather_df = self._generate_default_weather_data(target_dt)
            
            # 获取节假日信息
            holiday_info = self._get_holiday_info(target_dt)
            
            # 保存到数据库
            db = get_db_session()
            try:
                # 保存负荷数据
                load_saved = self._save_load_data(load_df, target_dt, db)
                
                # 保存气象数据
                weather_saved = self._save_weather_data(weather_df, db)
                
                # 保存节假日信息
                holiday_saved = self._save_holiday_info(holiday_info, db)
                
                db.commit()
                
                return {
                    'success': True,
                    'message': f'成功更新 {target_date} 的数据',
                    'details': {
                        'load_records': load_saved,
                        'weather_records': weather_saved,
                        'holiday_records': holiday_saved,
                        'date': target_date
                    }
                }
                
            except Exception as e:
                db.rollback()
                logger.error(f"保存数据到数据库失败: {e}")
                return {
                    'success': False,
                    'error': f'保存数据失败: {str(e)}'
                }
            finally:
                db.close()
                
        except Exception as e:
            logger.error(f"更新指定日期数据失败: {e}")
            return {
                'success': False,
                'error': str(e)
            }
    
    def _save_load_data(self, load_df: pd.DataFrame, target_date: date, db: Session) -> int:
        """保存负荷数据到数据库"""
        try:
            # 删除已存在的数据
            db.query(LoadData).filter(LoadData.dt == target_date).delete()
            
            # 准备96个时间点的数据
            load_records = []
            for t_idx in range(1, 97):
                # 计算对应的时间
                hour = (t_idx - 1) // 4
                minute = ((t_idx - 1) % 4) * 15
                target_time = datetime.combine(target_date, datetime.min.time()) + timedelta(hours=hour, minutes=minute)
                
                # 查找最接近的时间点数据
                closest_data = None
                min_diff = float('inf')
                
                for _, row in load_df.iterrows():
                    time_diff = abs((row['datetime'] - target_time).total_seconds())
                    if time_diff < min_diff:
                        min_diff = time_diff
                        closest_data = row
                
                # 创建负荷记录
                load_val = closest_data['load_value'] if closest_data is not None else 8000.0
                
                load_record = LoadData(
                    dt=target_date,
                    t_idx=t_idx,
                    load_val=float(load_val)
                )
                load_records.append(load_record)
            
            # 批量插入
            db.add_all(load_records)
            db.flush()
            
            logger.info(f"保存了 {len(load_records)} 条负荷数据")
            return len(load_records)
            
        except Exception as e:
            logger.error(f"保存负荷数据失败: {e}")
            raise
    
    def _save_weather_data(self, weather_df: pd.DataFrame, db: Session) -> int:
        """保存气象数据到数据库"""
        try:
            weather_records = []
            
            for _, row in weather_df.iterrows():
                # 检查是否已存在
                existing = db.query(WeatherDaily).filter(WeatherDaily.dt == row['dt']).first()
                
                if existing:
                    # 更新现有记录
                    setattr(existing, 't_max', float(row['t_max']))
                    setattr(existing, 't_min', float(row['t_min']))
                    setattr(existing, 'prec_sum', float(row['prec_sum']))
                    setattr(existing, 'humidity', float(row['humidity']))
                    setattr(existing, 'pressure', float(row['pressure']))
                    setattr(existing, 'wind_dir', float(row['wind_dir']))
                    setattr(existing, 'uv_index', float(row['uv_index']))
                    setattr(existing, 'snow', float(row['snow']))
                    setattr(existing, 'sun_dur', float(row['sun_dur']))
                    setattr(existing, 'sw_rad', float(row['sw_rad']))
                    setattr(existing, 'et0', float(row['et0']))
                else:
                    # 创建新记录
                    weather_record = WeatherDaily(
                        dt=row['dt'],
                        t_max=float(row['t_max']),
                        t_min=float(row['t_min']),
                        prec_sum=float(row['prec_sum']),
                        humidity=float(row['humidity']),
                        pressure=float(row['pressure']),
                        wind_dir=float(row['wind_dir']),
                        uv_index=float(row['uv_index']),
                        snow=float(row['snow']),
                        sun_dur=float(row['sun_dur']),
                        sw_rad=float(row['sw_rad']),
                        et0=float(row['et0'])
                    )
                    weather_records.append(weather_record)
            
            if weather_records:
                db.add_all(weather_records)
                db.flush()
            
            logger.info(f"保存了 {len(weather_records)} 条气象数据")
            return len(weather_records)
            
        except Exception as e:
            logger.error(f"保存气象数据失败: {e}")
            raise
    
    def _save_holiday_info(self, holiday_info: Dict, db: Session) -> int:
        """保存节假日信息到数据库"""
        try:
            # 检查是否已存在
            existing = db.query(HolidayInfo).filter(HolidayInfo.dt == holiday_info['dt']).first()
            
            if existing:
                # 更新现有记录
                setattr(existing, 'is_holiday', holiday_info['is_holiday'])
                setattr(existing, 'h_name', holiday_info['holiday_name'])
                return 0
            else:
                # 创建新记录
                holiday_record = HolidayInfo(
                    dt=holiday_info['dt'],
                    is_holiday=holiday_info['is_holiday'],
                    h_name=holiday_info['holiday_name']
                )
                db.add(holiday_record)
                db.flush()
                return 1
                
        except Exception as e:
            logger.error(f"保存节假日信息失败: {e}")
            raise
    
    def _get_holiday_info(self, target_date: date) -> Dict:
        """获取节假日信息"""
        try:
            is_holiday_flag = is_holiday(target_date)
            holiday_name = "节假日" if is_holiday_flag else "工作日"
            
            return {
                'dt': target_date,
                'is_holiday': is_holiday_flag,
                'holiday_name': holiday_name
            }
        except Exception as e:
            logger.error(f"获取节假日信息失败: {e}")
            return {
                'dt': target_date,
                'is_holiday': False,
                'holiday_name': "工作日"
            }
    
    def _generate_default_weather_data(self, target_date: date) -> pd.DataFrame:
        """生成默认气象数据"""
        return pd.DataFrame([{
            'dt': target_date,
            't_max': 20.0,
            't_min': 10.0,
            'prec_sum': 0.0,
            'humidity': 70.0,
            'pressure': 1013.0,
            'wind_dir': 180.0,
            'uv_index': 3.0,
            'snow': 0.0,
            'sun_dur': 8.0,
            'sw_rad': 10.0,
            'et0': 2.0
        }]) 